package cn.edu.zjgsu.ito.service;

import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class AdminExportStuService {
	
	public Record TTT(String uuid){
		 try {
				Record record=Db.findFirst("select * from student_timetable where uuid=?",uuid);
//				System.out.println(uuid+"           "+record);
				return record;
			} catch (Exception e) {
				e.printStackTrace();
				return null;
			}
		 
	 }
	
	public List<Record> CourseStu(String uuid) {
		try {
			
			List<Record> records=Db.find("select sNo,lastName,firstName from roster where uuid=?",uuid);
			
			return records;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

	}
	
	public List<Record> test(String uuid){
		 try {
				List<Record> record=Db.find("select * from teacher_timetable,teacher_info where teacher_timetable.tNo=teacher_info.tNo and uuid=?",uuid);
//				System.out.println(uuid+"           "+record);
				return record;
			} catch (Exception e) {
				e.printStackTrace();
				return null;
			}
		 
	 }
	

	 /**
	  *  创建excel
	  *  课程学生名单导出
	  *  杨昆鹏
	  * */
	 public void exportCouStuExcel(List<Record> record,List<Record> records, OutputStream out){
		 	
//		 	System.out.println("--------------------");
			//创建一个Excel工作薄
			HSSFWorkbook book = new HSSFWorkbook();
			//在Excel工作薄中建立一张工作表
			HSSFSheet sheet = book.createSheet("学生名单");
			//设置单元格格式（文本）
			HSSFCellStyle cellStyle = book.createCellStyle();	
//			System.out.println("----------1----------");
			//设计这些格式
			cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);//居中
			cellStyle.setWrapText(true);//自动换行
			sheet.setColumnWidth((short) 4, (short) 21*256);
//			System.out.println("-----------2---------");
			//设置字体
			HSSFFont font = book.createFont();
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体
			cellStyle.setFont(font);
//			System.out.println("-----------3---------");
			
			HSSFCellStyle cellStyle1 = book.createCellStyle();
			cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
			cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
//			cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);//居中
			cellStyle.setWrapText(true);//自动换行
			
			//第一行为标题
			HSSFRow row0 = sheet.createRow(0);//创建第一行
			row0.setHeight((short) (3*256));//行高
			HSSFCell cell01 = row0.createCell(0);
			HSSFCell cell02 = row0.createCell(1);
			HSSFCell cell03 = row0.createCell(2);
			HSSFCell cell04 = row0.createCell(3);
			HSSFCell cell05 = row0.createCell(4);
			//定义单元格字符串类型
			cell01.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell02.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell03.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell04.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell05.setCellType(HSSFCell.CELL_TYPE_STRING);
			//设计格式
			cell01.setCellStyle(cellStyle);
			cell02.setCellStyle(cellStyle);
			cell03.setCellStyle(cellStyle);
			cell04.setCellStyle(cellStyle);
			cell05.setCellStyle(cellStyle);
			//在单元格中输入数据
			cell01.setCellValue("学期");
			cell02.setCellValue("课程名"); 
			cell03.setCellValue("教师");
			cell04.setCellValue("教室");
			cell05.setCellValue("助教");
//			System.out.println("----------4----------");
			//第二行 获取标题对应的数据
			HSSFRow row1 = sheet.createRow(1);//创建第二行
			row1.setHeight((short) (3*256));//行高
			HSSFCell cell11 = row1.createCell(0);
			HSSFCell cell12 = row1.createCell(1);
			HSSFCell cell13 = row1.createCell(2);
			HSSFCell cell14 = row1.createCell(3);
			HSSFCell cell15 = row1.createCell(4);
			//定义单元格字符串类型
			cell11.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell12.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell13.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell14.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell15.setCellType(HSSFCell.CELL_TYPE_STRING);
			//设计格式
			cell11.setCellStyle(cellStyle);
			cell12.setCellStyle(cellStyle);
			cell13.setCellStyle(cellStyle);
			cell14.setCellStyle(cellStyle);
			cell15.setCellStyle(cellStyle);
			//在单元格中输入数据
			String uuid = record.get(0).getStr("uuid");
			String assistant_tName = Db.findFirst("select * from major_timetable where uuid=?",uuid).getStr("assistant_tName");
			String temp = record.get(0).getStr("tName");
			String classes = record.get(0).getStr("classroomNo");
			if(record.size() >1){
				classes = classes + "," + record.get(1).getStr("classroomNo");
			}
			cell11.setCellValue(record.get(0).getStr("termValue"));
			cell12.setCellValue(record.get(0).getStr("courseName")); 
			cell13.setCellValue(temp);
			cell14.setCellValue(classes);
			cell15.setCellValue(assistant_tName);
//			System.out.println("---------5-----------");
			//第三行
			HSSFRow row2 = sheet.createRow(2);//创建第三行
			row2.setHeight((short) (3*256));//行高
			HSSFCell cell20 = row2.createCell(0);
			HSSFCell cell21 = row2.createCell(1);
			HSSFCell cell22 = row2.createCell(2);
			HSSFCell cell23 = row2.createCell(3);
			//定义单元格字符串类型
			cell20.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell21.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell22.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell23.setCellType(HSSFCell.CELL_TYPE_STRING);
			//设计格式
			cell20.setCellStyle(cellStyle);
			cell21.setCellStyle(cellStyle);
			cell22.setCellStyle(cellStyle);
			cell23.setCellStyle(cellStyle);
			//在单元格中输入数据
			cell20.setCellValue("序号");
			cell21.setCellValue("学号");
			cell22.setCellValue("Lastname"); 
			cell23.setCellValue("Firstname"); 
//			System.out.println("---------6-----------");
			//循环数据到excel中
			
			sheet.setColumnWidth((short) 0, (short) 21*256);
			sheet.setColumnWidth((short) 1, (short) 21*256);
			sheet.setColumnWidth((short) 2, (short) 21*256);
			sheet.setColumnWidth((short) 3, (short) 21*256);
			for(int i = 0;i < records.size();i++){
				
				HSSFRow row = sheet.createRow(i+3);
				HSSFCell cell0 = row.createCell(0);
				HSSFCell cell1 = row.createCell(1);
				HSSFCell cell2 = row.createCell(2);
				HSSFCell cell3 = row.createCell(3);
				
				cell0.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell3.setCellType(HSSFCell.CELL_TYPE_STRING);
				
				cell0.setCellStyle(cellStyle1);
				cell1.setCellStyle(cellStyle1);
				cell2.setCellStyle(cellStyle1);
				cell3.setCellStyle(cellStyle1);
				
				cell0.setCellValue(i+1);
				cell1.setCellValue(records.get(i).getStr("sNo"));
				cell2.setCellValue(records.get(i).getStr("lastName"));
				cell3.setCellValue(records.get(i).getStr("firstName"));
				
			}
//			System.out.println("----------7----------");
			try {
				//写入数据 把相应的Excel工作薄存盘
				book.write(out);
				
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				try {
					out.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
	 }
}
